******SET YOUR DIRECTORY to ...\replication



clear all


use "intermediatedata\bridge_final.dta", clear

*Generate FIPS county code
gen fipscounty_00="0"+string(FSTATE)+"00"+string(FCOUNTY) if FSTATE<10 & FCOUNTY<10
replace fipscounty_00=string(FSTATE)+"00"+string(FCOUNTY) if FSTATE>=10 & FSTATE!=. & FCOUNTY<10
replace fipscounty_00="0"+string(FSTATE)+"0"+string(FCOUNTY) if FSTATE<10 & FCOUNTY>=10 & FCOUNTY<100
replace fipscounty_00=string(FSTATE)+"0"+string(FCOUNTY) if FSTATE>=10 & FSTATE!=. & FCOUNTY>=10 & FCOUNTY<100
replace fipscounty_00="0"+string(FSTATE)+string(FCOUNTY) if FSTATE<10 & FCOUNTY>=10 & FCOUNTY>=100 & FCOUNTY!=.
replace fipscounty_00=string(FSTATE)+string(FCOUNTY) if FSTATE>=10 & FSTATE!=. & FCOUNTY>=10 & FCOUNTY>=100 & FCOUNTY!=.
destring fipscounty_00, replace force
*Generate FIPS place code
gen fipsplace_00="0"+string(FSTATE)+"000"+string(FPLACE) if FSTATE<10 & FPLACE<100
replace fipsplace_00=string(FSTATE)+"000"+string(FPLACE) if FSTATE>=10 & FSTATE!=. & FPLACE<100
replace fipsplace_00="0"+string(FSTATE)+"00"+string(FPLACE) if FSTATE<10 & FPLACE<1000 & FPLACE>=100
replace fipsplace_00=string(FSTATE)+"00"+string(FPLACE) if FSTATE>=10 & FSTATE!=. & FPLACE<1000 & FPLACE>=100
replace fipsplace_00="0"+string(FSTATE)+"0"+string(FPLACE) if FSTATE<10 & FPLACE<10000 & FPLACE>=1000
replace fipsplace_00=string(FSTATE)+"0"+string(FPLACE) if FSTATE>=10 & FSTATE!=. & FPLACE<10000 & FPLACE>=1000
replace fipsplace_00="0"+string(FSTATE)+string(FPLACE) if FSTATE<10 & FPLACE!=. & FPLACE>=10000
replace fipsplace_00=string(FSTATE)+string(FPLACE) if FSTATE>=10 & FSTATE!=. & FPLACE!=. & FPLACE>=10000
destring fipsplace_00, replace force

drop _merge

merge m:1 fipsplace_00 using "intermediatedata\data_geology_plc.dta"
drop if _merge !=3
drop _merge

gen fipsplace_cc_2=fipsplace_00 if city_center==1
egen fipsplace_cc=max(fipsplace_cc_2), by(FMSA)
drop fipsplace_cc_2

egen seq_plc=seq(), by(fipsplace_00)

egen ph_ncc_2=wtmean(ph1_plc_wtm_wtm_0_r) if city_center==0 & seq_plc==1, weight(area_plc) by(FMSA)
egen ph_ncc=max(ph_ncc_2), by(FMSA)
drop ph_ncc_2
egen area_ncc_2=total(area_plc) if city_center==0 & seq_plc==1,by(FMSA)
egen area_ncc=max(area_ncc_2), by(FMSA)
drop area_ncc_2

collapse ph_ncc area_ncc, by(fipsplace_cc)
drop if fipsplace_cc==.

rename fipsplace_cc fipsplace_00
label var ph_ncc "pH in NCC"
label var area_ncc "Area NCC"

save "rawdata\wss_gsmsoil_US_[2006-07-06]\data_geology_ncc.dta", replace
